package org.jypj.dev.util;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;

import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;

public class ImportExcel {

    private static Logger logger = Logger.getLogger(ImportExcel.class);

    /**
     * 获取单元格的值
     *
     * @param cell
     * @return
     */
    public static String getCellValue(Cell cell) {
        try {
            if (cell == null)
                return "";
            if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                return cell.getStringCellValue();
            } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
                return String.valueOf(cell.getBooleanCellValue());
            } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                return cell.getCellFormula();
            } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    Date d = cell.getDateCellValue();
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
                    return sdf.format(d);
                }
                return String.valueOf(cell.getNumericCellValue());
            }
            return "";
        } catch (Exception ex) {
            System.out.println(cell.toString());
            return "";
        }

    }

    /**
     * 改造poi默认的toString（）方法如下
     *
     * @param @param  cell
     * @param @return 设定文件
     * @return String    返回类型
     * @throws
     * @Title: getStringVal
     * @Description: 1.对于不熟悉的类型，或者为空则返回""控制串
     * 2.如果是数字，则修改单元格类型为String，然后返回String，这样就保证数字不被格式化了
     */
    public static String getStringVal(Cell cell) {
        String val = "";
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_BOOLEAN:
                return cell.getBooleanCellValue() ? "TRUE" : "FALSE";
            case Cell.CELL_TYPE_FORMULA:
                return cell.getCellFormula();
            case Cell.CELL_TYPE_NUMERIC:
                Double val3 = cell.getNumericCellValue();
                val = val3.toString();
                if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                    Date theDate = cell.getDateCellValue();
                    SimpleDateFormat dff = new SimpleDateFormat("HH:mm");
                    val = dff.format(theDate);
                } else {
                    DecimalFormat df = new DecimalFormat("0");
                    val = df.format(cell.getNumericCellValue());
                }
                return val;
            case Cell.CELL_TYPE_STRING:
                return cell.getStringCellValue();
            default:
                return "";
        }
    }

    /**
     * 日期
     * @param cell
     * @return
     */
    public static String getStringValDate(Cell cell) {
        String val = "";
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_BOOLEAN:
                return cell.getBooleanCellValue() ? "TRUE" : "FALSE";
            case Cell.CELL_TYPE_FORMULA:
                return cell.getCellFormula();
            case Cell.CELL_TYPE_NUMERIC:
                Double val3 = cell.getNumericCellValue();
                val = val3.toString();
                if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                    Date theDate = cell.getDateCellValue();
                    SimpleDateFormat dff = new SimpleDateFormat("yyyy-MM-dd");
                    val = dff.format(theDate);
                } else {
                    DecimalFormat df = new DecimalFormat("0");
                    val = df.format(cell.getNumericCellValue());
                }
                return val;
            case Cell.CELL_TYPE_STRING:
                return cell.getStringCellValue();
            default:
                return "";
        }
    }

    /**
     * @param sheet 表格
     * @param row   行号
     * @param col   //列号
     * @return
     */
    public static String getCellValueByIndex(Sheet sheet, int row, int col) {
        Cell cell = sheet.getRow(row).getCell(col);
        return cell == null ? "" : getStringVal(cell).trim();
    }


    public static String getCellValueByIndex2(Sheet sheet, int row, int col) {
        Cell cell = sheet.getRow(row).getCell(col);
        return cell == null ? "" : getStringValDate(cell).trim();
    }


}
